package com.mj.factory.processFlow.Technology;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.util.Collection;
import java.util.Iterator;


/**
 * Created by Administrator on 2017/8/31.
 */
public class ExportExcel<T> {
    public void exportExcel(String[] bottom, String[] title, String[] header0, String[] header1, String[] header2, String[] fieldNameArr, Collection<T> dataset, String fileName, String path, HttpServletResponse response) {

        // 声明一个工作薄
        XSSFWorkbook workbook = new XSSFWorkbook();

        // 生成一个表格
        XSSFSheet sheet = workbook.createSheet("生产工艺流程卡");
        sheet.setColumnWidth(0, 4 * 256);
        sheet.setColumnWidth(1, 9 * 256);
        sheet.setColumnWidth(2, 37 * 256);
        sheet.setColumnWidth(3, 13 * 256);
        sheet.setColumnWidth(4, 13 * 256);
        sheet.setColumnWidth(5, 13 * 256);
        sheet.setColumnWidth(6, 15 * 256);
        sheet.setColumnWidth(7, 13 * 256);
        sheet.setDefaultRowHeightInPoints(27);
        //生成样式
        XSSFCellStyle style = workbook.createCellStyle();
        //字体
        Font font = workbook.createFont();
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) 12);
        style.setFont(font);
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中
        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setBorderLeft(CellStyle.BORDER_THIN);
        style.setBorderRight(CellStyle.BORDER_THIN);

        /**
         * 生成表头
         */

        int rowNum = 0;
        int rowCount = 5;

        XSSFRow row;
        try {
            for (int i = rowNum; i < rowCount; i++) {
                row = sheet.createRow(rowNum);
                int cellNum = 0;
                if (i < 2) {

                    //改变每列占多少个单元格
                    sheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum, 0, header2.length - 1));

                    XSSFCell cell = row.createCell(0);
                    XSSFRichTextString text = new XSSFRichTextString(title[i]);
                    cell.setCellValue(text);
                    cell.setCellStyle(style);
                } else if (rowNum >= 2 && rowNum < 4) {
                    String[] header = null;
                    if (rowNum == 2) {
                        header = header0;
                    } else if (rowNum == 3) {
                        header = header1;
                    }
                    for (int j = cellNum; j < header.length; j++) {
                        XSSFCell cell;
                        if (j == 0) {
                            sheet.addMergedRegion(new CellRangeAddress(i, i, j, j + 1));
                            cell = row.createCell(j);
                            XSSFRichTextString text = new XSSFRichTextString(header[j]);
                            cell.setCellValue(text);
                            cell.setCellStyle(style);
                        }
                        if (j == 1) {
                            cell = row.createCell(2);
                            XSSFRichTextString text = new XSSFRichTextString(header[j]);
                            cell.setCellValue(text);
                            cell.setCellStyle(style);
                        }
                        if (j == 2) {
                            sheet.addMergedRegion(new CellRangeAddress(i, i, j + 1, j + 2));
                            cell = row.createCell(j + 1);
                            XSSFRichTextString text = new XSSFRichTextString(header[j]);
                            cell.setCellValue(text);
                            cell.setCellStyle(style);
                        }
                        if (j == 3) {
                            sheet.addMergedRegion(new CellRangeAddress(i, i, j + 2, j + 4));
                            cell = row.createCell(j + 2);
                            XSSFRichTextString text = new XSSFRichTextString(header[j]);
                            cell.setCellValue(text);
                            cell.setCellStyle(style);
                        }


                    }

                } else {
                    for (int j = cellNum; j < header2.length; j++) {
                        XSSFCell cell = row.createCell(j);
                        XSSFRichTextString text = new XSSFRichTextString(header2[j]);
                        cell.setCellValue(text);
                        cell.setCellStyle(style);
                    }
                }
                rowNum++;
            }
        } catch (Exception e) {

            System.out.println(e);
        }

        try {
            // 遍历集合数据，产生数据行
            Iterator<T> it = dataset.iterator();
            while (it.hasNext()) {
                row = sheet.createRow(rowNum);
                T t = (T) it.next();
                // 利用反射，根据javabean属性的先后顺序，动态调用getXxx()方法得到属性值
                Field[] fields = t.getClass().getDeclaredFields();
                for (short i = 0; i < fields.length; i++) {
                    Field field = fields[i];
                    String fieldName = field.getName();
                    for (int k = 0; k < fieldNameArr.length; k++) {
                        if (!fieldName.equals(fieldNameArr[k])) {
                            continue;
                        } else {
                            XSSFCell cell = row.createCell(k);

                            String getMethodName = "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
                            Class tCls = t.getClass();
                            Method getMethod = tCls.getMethod(getMethodName, new Class[]{});
                            Object value = getMethod.invoke(t, new Object[]{});
                            // 判断值的类型后进行强制类型转换
                            String textValue = null;
                            // 其它数据类型都当作字符串简单处理
                            if (value != null && value != "") {
                                textValue = value.toString();
                            }
                            if (textValue != null) {
                                XSSFRichTextString richString = new XSSFRichTextString(textValue);
                                cell.setCellValue(richString);
                                cell.setCellStyle(style);

                            }
                            break;
                        }
                    }


                }
                rowNum++;
            }
            for (int i = 0; i < bottom.length; i++) {
                if (i > 0) {
                    sheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum, 0, header2.length - 1));
                    XSSFRichTextString text = new XSSFRichTextString(bottom[i]);
                    row = sheet.createRow(rowNum);
                    XSSFCell cell = row.createCell(0);
                    cell.setCellValue(text);
                }
                XSSFRichTextString text = new XSSFRichTextString(bottom[i]);
                row = sheet.createRow(rowNum);
                XSSFCell cell = row.createCell(0);
                cell.setCellValue(text);
                rowNum++;
            }
            setBorder(sheet, style, header2.length, bottom.length);
            saveExcelFile(workbook, fileName, path);
            downLoadExcelFile(workbook, fileName, response);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     *
     * 方法说明:
     * @return
     */

    /**
     * 方法说明: 下载excel
     *
     * @return
     */
    public void downLoadExcelFile(XSSFWorkbook workbook, String fileName, HttpServletResponse response) throws IOException {
        ByteArrayOutputStream os = new ByteArrayOutputStream();
        workbook.write(os);
        byte[] content = os.toByteArray();
        InputStream is = new ByteArrayInputStream(content);
        // 设置response参数，可以打开下载页面
        response.reset();
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        response.setHeader("Content-Disposition", "attachment;filename=" + new String(fileName.getBytes(), "ISO8859-1") + ".xlsx");
        ServletOutputStream out = response.getOutputStream();
        BufferedInputStream bis = null;
        BufferedOutputStream bos = null;
        try {
            bis = new BufferedInputStream(is);
            bos = new BufferedOutputStream(out);
            byte[] buff = new byte[2048];
            int bytesRead;
            // Simple read/write loop.
            while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
                bos.write(buff, 0, bytesRead);
            }
        } catch (final IOException e) {
            e.printStackTrace();
        } finally {
            if (bis != null)
                bis.close();
            if (bos != null)
                bos.close();
        }

    }

    public void saveExcelFile(XSSFWorkbook workbook, String fileName, String path) throws Exception {
        OutputStream out = null;

        try {
            File file = new File(path);
            if (!file.exists() || !file.isDirectory()) {
                file.mkdirs();
            }
            out = new FileOutputStream(path + File.separator + fileName + ".xlsx");
            workbook.write(out);

        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (out != null) {
                try {
                    out.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    public void setBorder(XSSFSheet sheet, XSSFCellStyle style, int columnNum, int ignoreRow) {
        for (int i = 0; i <= sheet.getLastRowNum() - ignoreRow; i++) {
            XSSFRow row = sheet.getRow(i);
            for (int m = 0; m < columnNum; m++) {
                XSSFCell cell = row.getCell(m);
                if (null != cell) {
                    cell.setCellStyle(style);
                } else {
                    cell = row.createCell(m);
                    cell.setCellStyle(style);
                }
            }
        }
    }

}
